package com.google.code.sharecontent.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import com.google.code.sharecontent.action.ResultContainer;
import com.google.code.sharecontent.dao.ContentDao;
import com.google.code.sharecontent.exception.ChangeStatusException;
import com.google.code.sharecontent.model.Content;
import com.google.code.sharecontent.model.Paging;
import com.google.code.sharecontent.model.Status;

public class ContentDaoImpl extends BaseDaoImpl<Content> implements ContentDao {

	public Content findByUrl(String url) throws SQLException {
		Content result = null;
		// List<Content> list = this.list("URL = '"+url+"'", "ID");
		List<Content> list = this.list("URL = ?", "ID", url);
		if (list != null && list.size() > 0) {
			result = list.get(0);
		}
		return result;
	}

	public ContentDaoImpl(Connection conn) {
		super(conn);
	}

	public boolean changeStatus(Content content, Status newStatus) throws ChangeStatusException, SQLException {
		if (content != null && newStatus != null) {
			if (this.changeStatus(content.getId(), newStatus)) {
				content.setStatusId(newStatus.getId());
				return true;
				
			}
		}
		return false;
	}

	public boolean changeStatus(Integer contentId, Status newStatus) throws ChangeStatusException, SQLException {
		String sql = "select CODE from STATUSES where ID in (select STATUS_ID from CONTENTS where ID=?)";
		PreparedStatement statement = this.conn.prepareStatement(sql);
		statement.setInt(1, contentId);
		ResultSet rs = statement.executeQuery();
		String currentStatusCode = null;
		if (rs.next()) {
			currentStatusCode = rs.getString("CODE");
		}
		if (currentStatusCode != null && newStatus.getCode() != null) {
			List<String> nextStatuses = Status.nextContentStatuses.get(currentStatusCode);
			if (nextStatuses != null && nextStatuses.contains(newStatus.getCode())) {
				sql = "update CONTENTS set STATUS_ID = ? where ID = ?";
				statement = this.conn.prepareStatement(sql);
				statement.setInt(1, newStatus.getId());
				statement.setInt(2, contentId);
				if (statement.executeUpdate() > 0) {
					return true;
				}
			} else {
				throw new ChangeStatusException();
			}
		}
		return false;
	}

	@Override
	public Content createByRow(ResultSet rs) throws SQLException {
		Content content = new Content();
		content.setId(rs.getInt("ID"));
		content.setResourceId(rs.getInt("RESOURCE_ID"));
		content.setStatusId(rs.getInt("STATUS_ID"));
		content.setUrl(rs.getString("URL"));
		content.setTypeId(rs.getInt("TYPE_ID"));
		content.setTitle(rs.getString("TITLE"));
		content.setArtist(rs.getString("ARTIST"));
		content.setAlbum(rs.getString("ALBUM"));
		content.setYear(rs.getString("CONTENT_YEAR"));
		content.setGenre(rs.getString("GENRE"));
		content.setTrack(rs.getString("TRACK"));
		content.setSize(rs.getInt("SIZE"));
		content.setParamDate(rs.getString("PARAM_DATE"));
		content.setParamLastModifier(rs.getString("PARAM_LAST_MODIFIER"));
		content.setFilename(rs.getString("FILENAME"));
		return content;
	}

	@Override
	protected String generateInsertSql(Content status) {
		return "insert into "
				+ getTableName()
				+ " (RESOURCE_ID,STATUS_ID,URL,TYPE_ID,TITLE,ARTIST,ALBUM,CONTENT_YEAR,GENRE,TRACK,SIZE,PARAM_DATE,PARAM_LAST_MODIFIER,FILENAME) values "
				+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		/*
		 * return"insert into "+getTableName()+
		 * " (RESOURCE_ID,STATUS_ID,URL,TYPE_ID,TITLE,ARTIST,ALBUM,CONTENT_YEAR,GENRE,TRACK,SIZE,PARAM_DATE,PARAM_LAST_MODIFIER,FILENAME) values ("
		 * +""+status.getResourceId()+","+status.getStatusId()+","+
		 * getStringWithQuotesOrNull
		 * (status.getUrl())+","+status.getTypeId()+","+
		 * getStringWithQuotesOrNull(status.getTitle())+"," +
		 * ""+getStringWithQuotesOrNull
		 * (status.getArtist())+","+getStringWithQuotesOrNull
		 * (status.getAlbum())+
		 * ","+getStringWithQuotesOrNull(status.getYear())+","
		 * +getStringWithQuotesOrNull
		 * (status.getGenre())+","+getStringWithQuotesOrNull
		 * (status.getTrack())+"," +
		 * ""+status.getSize()+","+getStringWithQuotesOrNull
		 * (status.getParamDate(
		 * ))+","+getStringWithQuotesOrNull(status.getParamLastModifier
		 * ())+","+getStringWithQuotesOrNull(status.getFilename())+")";
		 */
	}

	protected PreparedStatement putPrepareStatement(PreparedStatement pStat, Content status) throws SQLException {
		pStat.setInt(1, status.getResourceId());
		pStat.setInt(2, status.getStatusId());
		pStat.setString(3, status.getUrl());
		pStat.setInt(4, status.getTypeId());
		pStat.setString(5, status.getTitle());
		pStat.setString(6, status.getArtist());
		pStat.setString(7, status.getAlbum());
		pStat.setString(8, status.getYear());
		pStat.setString(9, status.getGenre());
		pStat.setString(10, status.getTrack());
		if (status.getSize() != null) {
			pStat.setInt(11, status.getSize());
		} else {
			pStat.setNull(11, Types.INTEGER);
		}
		pStat.setString(12, status.getParamDate());
		pStat.setString(13, status.getParamLastModifier());
		pStat.setString(14, status.getFilename());
		return pStat;
	}

	@Override
	protected String generateUpdateSql(Content status) {
		return "update " + getTableName() + " set " + "RESOURCE_ID = ?," + "STATUS_ID = ?," + "URL = ?," + "TYPE_ID = ?," + "TITLE = ?,"
				+ "ARTIST = ?," + "ALBUM = ?," + "CONTENT_YEAR = ?," + "GENRE = ?," + "TRACK = ?," + "SIZE = ?," + "PARAM_DATE = ?,"
				+ "PARAM_LAST_MODIFIER = ?," + "FILENAME = ? " + " where ID = " + status.getId();
		/*
		 * return "update "+getTableName()+" set " +
		 * "RESOURCE_ID = "+status.getResourceId()+"," +
		 * "STATUS_ID = "+status.getStatusId()+"," +
		 * "URL = "+getStringWithQuotesOrNull(status.getUrl())+"," +
		 * "TYPE_ID = "+status.getTypeId()+"," +
		 * "TITLE = "+getStringWithQuotesOrNull(status.getTitle())+"," +
		 * "ARTIST = "+getStringWithQuotesOrNull(status.getArtist())+"," +
		 * "ALBUM = "+getStringWithQuotesOrNull(status.getAlbum())+"," +
		 * "CONTENT_YEAR = "+getStringWithQuotesOrNull(status.getYear())+"," +
		 * "GENRE = "+getStringWithQuotesOrNull(status.getGenre())+"," +
		 * "TRACK = "+getStringWithQuotesOrNull(status.getTrack())+"," +
		 * "SIZE = "+status.getSize()+"," +
		 * "PARAM_DATE = "+getStringWithQuotesOrNull(status.getParamDate())+","
		 * +"PARAM_LAST_MODIFIER = "+getStringWithQuotesOrNull(status.
		 * getParamLastModifier())+"," +
		 * "FILENAME = "+getStringWithQuotesOrNull(status.getFilename())+" " +
		 * " where ID = "+ status.getId();
		 */
	}

	@Override
	protected String getTableName() {
		return "CONTENTS";
	}

	public List<Content> listByStatus(Integer statusId) throws SQLException {
		return this.list("STATUS_ID = ? ", "ID", statusId);
	}

	public ResultContainer<Content> listByResourceAndStatus(Paging paging,Integer resourceId, Integer statusId) throws SQLException {
		return this.list(paging,"RESOURCE_ID = ? AND STATUS_ID = ? ", "ID", resourceId, statusId);
	}

	public ResultContainer<Content> listByResourceAndStatusAndPhrase(Paging paging,Integer resourceId, Integer statusId, String name) throws SQLException {
		SearchByPhraseQuery searchByPhraseQuery = searchByPhraseQuery(name);
		List<Object> values = new ArrayList<Object>();
		values.add(resourceId);
		values.add(statusId);
		values.addAll(Arrays.asList(searchByPhraseQuery.values));
		return this.list(paging,"RESOURCE_ID = ? AND STATUS_ID = ? AND (" + searchByPhraseQuery.subQuery.toString() + ")", "ID", values.toArray());
	}

	public List<Content> findByPhrase(String name) throws SQLException {
		return this.findByPhraseAndStatus(name, null, null);
	}

	@Override
	public List<Content> findByPhraseAndStatus(String name, Integer statusId, String notIncludedIds) throws SQLException {
		StringBuffer query = new StringBuffer();
		if (notIncludedIds != null && notIncludedIds.length() > 0) {
			query.append(" not ID in (").append(notIncludedIds).append(") ");
		}
		if (query.length() > 0) {
			query.append(" AND ");
		}
		if (statusId != null) {
			query.append(" STATUS_ID = ").append(statusId);
		}
		SearchByPhraseQuery searchByPhraseQuery = searchByPhraseQuery(name);
		return this.list(query.append(" and (").append(searchByPhraseQuery.subQuery).append(") ").toString(), "ID",
				searchByPhraseQuery.values);
	}

	private SearchByPhraseQuery searchByPhraseQuery(String name) {
		String[] columns = new String[] { "TITLE", "ARTIST", "ALBUM", "CONTENT_YEAR", "FILENAME" };
		SearchByPhraseQuery searchByPhraseQuery = new SearchByPhraseQuery();
		searchByPhraseQuery.values = new String[columns.length];
		searchByPhraseQuery.subQuery = new StringBuffer();
		int i = 0;
		for (String column : columns) {
			if (searchByPhraseQuery.subQuery.length() > 0) {
				searchByPhraseQuery.subQuery.append(" or ");
			}
			searchByPhraseQuery.subQuery.append("lower(").append(column).append(") like ? ");
			searchByPhraseQuery.values[i++] = "%" + name.toLowerCase() + "%";
			// subQuery.append("lower(").append(column).append(") like '%").append(name.toLowerCase()).append("%' ");
		}
		return searchByPhraseQuery;
	}

	class SearchByPhraseQuery {
		Object[] values;
		StringBuffer subQuery;
	}
}
